Using Connection Pooling

Connection pooling allows you to reuse connections rather than create a new one every time the data provider needs to establish a connection to the underlying database. The data provider automatically enables connection pooling for your .NET client application.

You can control connection pooling behavior by using connection string options (see "Specifying Connection Options"). For example, you can define the number of connection pools, the number of connections in a pool, and the lifetime of pooled connections.

Connection pooling in ADO.NET is not provided by the .NET Framework. It must be implemented in the .NET data provider itself.

Creating a Connection Pool

Each connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

The pool remains active as long as any connections remain open, either in the pool or used by an application with a reference to a Connection object that has an open connection.

If a new connection is opened and the connection string does not exactly match an existing pool, a new pool must be created. By using the same connection string, you can enhance the performance and scalability of your application.

In the following C# code fragment, three new SequeLinkConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ only by the value assigned for User ID and Password, and by the value of the Min Pool Size option.

SequeLinkConnection conn1 = new SequeLinkConnection(); 
conn.ConnectionString = "Host=Accounting; 
                    User ID=john;password=beach; 
                    Database=test;Min Pool Size=50"; 
conn1.Open(); 
// Pool A is created and filled with connections to 
// the minimum pool size.  
SequeLinkConnection conn2 = new SequeLinkConnection(); 
conn.ConnectionString = "Host=Accounting;
                     User ID=mary;password=jtb28cnc 
                     Database=test;Min Pool Size=100"; 
conn2.Open(); 
// Pool B is created because the connection strings differ. 
SequeLinkConnection conn3 = new SequeLinkConnection(); 
conn.ConnectionString = "Host=Accounting;
                    User ID=john;password=beach; 
                    Database=test;Min Pool Size=50"; 
conn3.Open(); 
// Conn3 is assigned an existing connection that was  
// created in Pool A when the pool was created for Conn1 

Adding Connections to a Pool

A connection pool is created in the process of creating each unique connection string that an application uses. When a pool is created, it is populated with enough connections to satisfy the minimum pool size requirement, set by the Min Pool Size connection string option. If an application is using more connections than Min Pool Size, the data provider allocates additional connections to the pool up to the value of the Max Pool Size connection string option, which sets the maximum number of connections in the pool.

When a SequeLinkConnection object is requested by the application calling the Connection.Open(...) method, the connection is obtained from the pool, if a usable connection is available. A usable connection is defined as a connection that is not currently in use by another valid SequeLinkConnection object, has a matching distributed transaction context (if applicable), and has a valid link to the server.

If the maximum pool size has been reached and no usable connection is available, the request is queued in the data provider. The data provider waits for the value of the Connection Timeout connection string option for a usable connection to return to the application. If this time period expires and no connection has become available, then the data provider returns an error to the application.

IMPORTANT: Closing the connection using the Close() or Dispose() method of the Connection object adds or returns the connection to the pool. When the application uses the Close method, the connection string settings remain as they were before the Open was called. If you use the Dispose method to close the connection, the connection string settings are cleared, and the default settings are restored.

Removing Connections from a Pool

A connection is removed from a connection pool when it either exceeds its lifetime as determined by the Connection Lifetime connection string option, or when a new connection is initiated by the application (SequeLinkConnection.Open() is called) that has a matching connection string.

Before returning a connection from the connection pool to an application, the Pool Manager checks to see if the connection has been closed at the server. If the connection is no longer valid, the Pool Manager discards it and returns another connection from the pool, if one is available and valid.

NOTE: By default, if discarding an invalid connection causes the number of connections to drop below the number specified in the Min Pool Size option, a new connection is not created until an application needs one.

Handling Dead Connection in a Pool

What happens when an idle connection loses its physical connection to the database? For example, suppose the database server is rebooted or the network experiences a temporary interruption. An application that attempts to connect using an existing Connection object from a pool could receive errors because the physical connection to the database has been lost.

The .NET data provider handles this situation transparently to the user. The application does not receive any errors on the Connection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the Connection object is used to execute a SQL statement (for example, through one of the DataReader execution methods or the DataAdapter.Fill method), the data provider detects that the physical connection to the server has been lost and attempts to reconnect to the server before executing the SQL statement. If the data provider can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The data provider uses the connection failover options, if enabled, when attempting this seamless reconnection. See "Using Connection Failover" for information about configuring the data provider to connect to a backup server when the primary server is not available.

NOTE: Because the .NET Client can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application when a statement is executed. If the .NET Client cannot reconnect to the server (for example, because the server is still down), the execution method throws an error indicating that the reconnect attempt failed, along with specifics about the reason the connection failed.

DataDirect's method of handling dead connections in connection pools allows for the maximum performance out of the connection pooling mechanism. Some data providers periodically ping the server with a dummy SQL statement while the connections sit idle. Other data providers ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add round trips to the database server and ultimately slow down the application during normal operation of the application is occurring.

Handling Distributed Transactions in a Pool

The Pool Manager groups the connections according to the requirement for transactions. If the requesting thread requires a specific transaction context, it must be matched to a connection with the same transaction context, for example, a connection that has been enlisted in distributed transactions.

Because closed connections are returned to the appropriate connection pool, you can close a connection even though a distributed transaction is pending. This means that you can still commit or roll back the distributed transaction until the connection is closed at the server.

See "Using Distributed Transactions" for more information about how the data provider processes distributed transactions

Tracking Connection Pool Performance

All DataDirect ADO.NET data providers install a set of PerfMon counters that let you tune and debug applications that use the data provider. See "PerfMon Support" for information about using the PerfMon counters.